from flask import Flask, render_template, request,jsonify
import pymysql
import time
app = Flask(__name__)

def select():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = "select * from employee limit 0,10;"
    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    cursor.close()
    conn.close()
    return data_list


def food_select(num):

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order=''
    if num==1:
        order="select * from dish where CATEGORY like'%荤菜%' and visible=1;"
    elif num==2:
        order = "select * from dish where CATEGORY='素菜' and visible=1;"
    elif num==3:
        order = "select * from dish where CATEGORY='主食' and visible=1;"
    elif num==4:
        order = "select * from dish where CATEGORY='饮品' and visible=1;"
    elif num==5:
        order = "select * from dish where CATEGORY='甜品' and visible=1;"
    elif num==6:
        order = "select * from dish where CATEGORY='汤类' and visible=1;"
    elif num==7:
        order = "select * from dish where CATEGORY='凉菜' and visible=1;"

    cursor.execute(order)
    data_list = cursor.fetchall()
    #print(data_list)
    cursor.close()
    conn.close()
    return data_list


@app.route('/index', methods=['GET', 'POST'])
def index():
    #data=select()
    return render_template('test_pages/demo.html')#返回一个界面,请一定要注意文件的相对路径是否正确,html文件必须要在templates文件夹里面，想要自定义位置请学习教程


@app.route('/ajax', methods=['GET', 'POST'])
def ajax():
    list=select()
    return jsonify(list)#将list包装成json（字典）发送回去


@app.route('/datatable', methods=['GET', 'POST'])
def datatable():
    list=select()
    return render_template('test_pages/data_tables练习.html', data=list)#flask的模板语言，可以不用了解






@app.route('/getMenu')
def getMenu():
    id=int(request.args.get('id'))
    list=food_select(id)
    return jsonify(list)

@app.route('/register')
def register():
    name=request.args.get('name')
    password=request.args.get('password')
    address=request.args.get('address')
    phone=request.args.get('phone')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = 'insert into customer(cname,password,clocation,cphone) values (%s,%s,%s,%s)'

    try:
        cursor.execute(order, [name, password, address, phone])
        conn.commit()
    except pymysql.err.IntegrityError:
        cursor.close()
        conn.close()
        return 'no'

    cursor.close()
    conn.close()
    print(name,password,address,phone)

    return 'yes'

@app.route('/login')
def login():
    name=request.args.get('name')
    password=request.args.get('password')

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = 'select password p from customer where cname=%s'


    cursor.execute(order, [name, ])
    cursor.close()
    conn.close()
    pwd = cursor.fetchone()
    if(pwd['p']==password):
        return 'yes'
    return 'no'

@app.route('/order',methods=['POST'])
def order():
    t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    get_json = request.get_json()
    print(get_json)
    name=get_json['name']
    items=get_json['item']
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    for item in items:
        for i in range(item[3]):
            order = 'insert into ord(cname,dname,price,deltime) values(%s,%s,%s,%s)'
            cursor.execute(order, [name,item[0],item[1],t])
            conn.commit()
    print(name,items,t)


    cursor.close()
    conn.close()

    return 'yes'

@app.route('/search')
def search():
    name=request.args.get('name')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8',
                           db='restaurant')  # 链接mysql
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    order = '''select * from dish where dname like '%{}%' ;'''.format(name)
    #print(name)
    cursor.execute(order)
    list = cursor.fetchall()
    print(list)
    cursor.close()
    conn.close()

    return jsonify(list)



if __name__=='__main__':
    #app.run(debug=True, host='127.0.0.1',port='8848')
    app.run(debug=True,host='192.168.43.181',port='8848')